#include "stdafx.h"
#include "DatabaseManager.h"
#include <memory>
#include <exception>
#include <string>
#include <list>
#include <set>
#include <sstream>
#include <iomanip>
#include <vector>
#include "sqlite3.h"
#include "IReading.h"
#include "Reading.h"
#include "ITime.h"
#include "Time.h"


#include <iostream>


/*
FIXME: tables not completely normalized for reading type. inefficient.
*/
namespace Sensor {

	const std::string DatabaseManager::READING_TABLE("readings");
	const std::string DatabaseManager::READING_ID("readingId");
	const std::string DatabaseManager::READING_SENSOR_ID("sensorId");
	const std::string DatabaseManager::READING_TYPE("type");
	const std::string DatabaseManager::READING_TIME("time");
	const std::string DatabaseManager::READING_VALUE("value");

	DatabaseManager::DatabaseManager() {
		database = nullptr;
	}

	/*
	Convert time to ISO 8601 YYYY-MM-DD HH:MM:SS string used by database.
	*/
	std::string DatabaseManager::convertTimeToString(const ITime& time) const {
		std::ostringstream timeStr;
		timeStr.fill('0');
		timeStr << std::setw(4) << time.getYear();
		timeStr << "-" << std::setw(2) << time.getMonth();
		timeStr << "-" << std::setw(2) << time.getDay();
		timeStr << " ";
		timeStr << std::setw(2) << time.getHour() << ":";
		timeStr << std::setw(2) << time.getMinute() << ":";
		timeStr << std::setw(2) << time.getSecond();
		return timeStr.str();
	}

	/*
	Get time from ISO 8601 YYYY-MM-DD HH:MM:SS string used by database.
	*/
	std::unique_ptr<ITime> DatabaseManager::convertStringToTime(std::string timeStr) const {
		int year = atoi(timeStr.substr(0, 4).c_str());
		int month = atoi(timeStr.substr(5, 2).c_str());
		int day = atoi(timeStr.substr(8, 2).c_str());
		int hour = atoi(timeStr.substr(11, 2).c_str());
		int minute = atoi(timeStr.substr(14, 2).c_str());
		int second = atoi(timeStr.substr(17, 2).c_str());

		return std::unique_ptr<ITime>(new Time(year, month, day, hour, minute, second));
	}

	void DatabaseManager::open(std::string filename, bool overwrite) {
		int result;

		result = sqlite3_open_v2(
			filename.c_str(),
			&database,
			SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX,
			nullptr
			);

		std::cout << result << std::endl;
		if (result != SQLITE_OK) {
			throw std::exception("Could not open or create database.");
		}

		// FIXME: drop tables if overwrite is true

		// CREATE TABLE IF NOT EXISTS readings (
		//		readingId INTEGER PRIMARY KEY NOT NULL,
		//		sensorId INTEGER NOT NULL,
		//		readingType VARCHAR(255) NOT NULL,
		//		time VARCHAR(20) NOT NULL,
		//		value REAL NOT NULL
		//		)
		std::string createSql("CREATE TABLE IF NOT EXISTS ");
		createSql += READING_TABLE + " ";
		createSql += "(";
		createSql += READING_ID + " INTEGER PRIMARY KEY NOT NULL, ";
		createSql += READING_SENSOR_ID + " INTEGER NOT NULL, ";
		createSql += READING_TYPE + " VARCHAR(255) NOT NULL, ";
		createSql += READING_TIME + " VARCHAR(20) NOT NULL, ";
		createSql += READING_VALUE + " REAL NOT NULL";
		createSql += ")";

		std::cout << createSql.c_str() << std::endl;

		sqlite3_stmt* statement;
		result = sqlite3_prepare_v2(
			database,
			createSql.c_str(),
			-1,
			&statement,
			nullptr
			);

		std::cout << result << std::endl; 

		if (result != SQLITE_OK) {
			throw std::exception("Problem preparing create table statement.");
		}

		result = sqlite3_step(statement);
		if (result != SQLITE_DONE) {
			throw std::exception("Problem creating tables.");
		}

		if (sqlite3_finalize(statement) != SQLITE_OK) {
			throw std::exception("Problem finalizing create table statement.");
		}

		// Create an index
	}

	void DatabaseManager::insertReading(IReading& reading) {
		int result;

		// Format:
		// INSERT INTO <READING_TABLE>(<READING_SENSOR_ID>, <READING_VALUE>)
		//    VALUES (<reading.getSensorId()>, <reading.getValue()>)

		std::ostringstream insertSql;
		insertSql << "INSERT INTO " << READING_TABLE;
		insertSql << "(";
		insertSql << READING_SENSOR_ID << ", ";
		insertSql << READING_TYPE << ", ";
		insertSql << READING_TIME << ", ";
		insertSql << READING_VALUE;
		insertSql << ") VALUES (";
		insertSql << reading.getSensorId() << ", ";
		insertSql << "'" << reading.getType() << "', ";
		insertSql << "'" << convertTimeToString(*reading.getTime()) << "', ";
		insertSql << reading.getValue();
		insertSql << ")";

		// Convert the SQL to a string.
		// Note: can't use insertSql.str().c_str() because insertSql.str() returns
		// a temporary string reference.
		const std::string insertSqlStr = insertSql.str();

		std::cout << insertSqlStr << std::endl;

		sqlite3_stmt* statement;
		result = sqlite3_prepare_v2(
			database,
			insertSqlStr.c_str(),
			-1,
			&statement,
			nullptr
			);

		if (result != SQLITE_OK) {
			throw std::exception("Problem inserting reading record 1.");
		}

		result = sqlite3_step(statement);
		if (result != SQLITE_DONE) {
			throw std::exception("Problem inserting reading record 2.");
		}

		result = sqlite3_finalize(statement);
		if (result != SQLITE_OK) {
			throw std::exception("Problem inserting reading record 3.");
		}
	}

	std::list<std::unique_ptr<IReading>> DatabaseManager::getReadings(const int sensorId, const std::string readingType, const ITime& start, const ITime& end) const {
		int result;

		// Format:
		// SELECT sensorId, type, time, value FROM readings WHERE sensorId=... AND type=... AND time BETWEEN ... AND ...
		// FIXME: ORDER BY 
		std::ostringstream sql;
		sql << "SELECT " << READING_SENSOR_ID << ", " << READING_TYPE << ", " << READING_TIME << ", " << READING_VALUE << " ";
		sql << "FROM " << READING_TABLE << " ";
		sql << "WHERE ";
		sql << READING_SENSOR_ID << "=" << sensorId << " AND ";
		sql << READING_TYPE << "='" << readingType << "' AND ";
		sql << READING_TIME << " BETWEEN '" << convertTimeToString(start) << "' AND '" << convertTimeToString(end) << "'";

		// Convert the SQL to a string.
		// Note: can't use sql.str().c_str() because sql.str() returns
		// a temporary string reference that get deallocated.
		std::string sqlStr = sql.str();

		std::cout << sqlStr << std::endl;

		sqlite3_stmt* statement;
		result = sqlite3_prepare_v2(
			database,
			sqlStr.c_str(),
			-1,
			&statement,
			nullptr
			);

		//double sum = 0; // Running sum of samples for current time segment
		//int count = 0; // Running sample count for current time segment
		//char endTimeStr[20]; // End time for current time segment in YYYY-MM-DD HH:MM:SS
		std::list<std::unique_ptr<IReading>> readings;
		while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
			int sensorId = sqlite3_column_int(statement, 0);
			const char* type = (const char*)sqlite3_column_text(statement, 1);
			const char* timeStr = (const char*)sqlite3_column_text(statement, 2);
			std::unique_ptr<ITime> time = convertStringToTime(timeStr);
			//delete [] timeStr; // FIXME: deletes?
			double value = sqlite3_column_double(statement, 3);

			readings.push_back(std::unique_ptr<IReading>(
				new Reading(
					sensorId,
					time,
					type,
					value
				)
				));

			//delete[] type;
		}

		if (result != SQLITE_DONE) {
			throw std::exception("Problem. 1");
		}

		result = sqlite3_finalize(statement);
		if (result != SQLITE_OK) {
			throw std::exception("Problem inserting reading record 3.");
		}

		return readings;
	}

	std::set<std::string> DatabaseManager::getReadingTypes() const {
		int result;

		std::string sql;
		sql += "SELECT DISTINCT " + READING_TYPE + " FROM " + READING_TABLE;

		// Prepare the statement
		sqlite3_stmt* statement;
		result = sqlite3_prepare_v2(
			database,
			sql.c_str(),
			-1,
			&statement,
			nullptr
			);

		if (result != SQLITE_OK) {
			throw std::exception("Problem 1.");
		}

		std::set<std::string> readingTypes;
		while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
			const char* type = (const char*) sqlite3_column_text(statement, 0);
			readingTypes.insert(type);
		}

		if (result != SQLITE_DONE) {
			throw std::exception("Problem 2.");
		}

		result = sqlite3_finalize(statement);
		if (result != SQLITE_OK) {
			throw std::exception("Problem 3");
		}

		return readingTypes;
	}

	std::set<int> DatabaseManager::getSensorIds(const ITime& start, const ITime& end) const {
		int result;

		// Format: SELECT DISTINCT <READING_SENSOR_ID> FROM <READING_TABLE>
		std::string sql;
		sql += "SELECT DISTINCT " + READING_SENSOR_ID + " FROM " + READING_TABLE + " ";
		sql += "WHERE " + READING_TIME + " BETWEEN '" + convertTimeToString(start) + "' AND '" + convertTimeToString(end) + "'";

		std::cout << sql << std::endl;

		// Prepare the statement
		sqlite3_stmt* statement;
		result = sqlite3_prepare_v2(
			database,
			sql.c_str(),
			-1,
			&statement,
			nullptr
			);

		if (result != SQLITE_OK) {
			throw std::exception("Problem.");
		}

		std::set<int> sensorIds;
		while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
			int sensorId = sqlite3_column_int(statement, 0);
			sensorIds.insert(sensorId);
		}

		if (result != SQLITE_DONE) {
			throw std::exception("Problem.");
		}

		result = sqlite3_finalize(statement);
		if (result != SQLITE_OK) {
			throw std::exception("Problem");
		}

		return sensorIds;
	}

	void DatabaseManager::close() {
		if (sqlite3_close_v2(database) != SQLITE_OK) {
			throw std::exception("Problem closing database connection.");
		}
	}

}